const mysql = require('mysql2/promise');
require('dotenv').config({ path: './config.env' });

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  port: process.env.DB_PORT || 3306,
  multipleStatements: true
};

async function initDatabase() {
  let connection;
  
  try {
    // 连接MySQL（不指定数据库）
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 连接到MySQL服务器成功');

    // 创建数据库
    const dbName = process.env.DB_NAME || 'intelligent_advisory';
    // 使用反引号包裹数据库名，以防特殊字符
    await connection.execute(`CREATE DATABASE IF NOT EXISTS \`${dbName}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    console.log(`✅ 数据库 ${dbName} 创建成功`);

    // 使用数据库
    await connection.query(`USE \`${dbName}\``);

    // 创建用户表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        name VARCHAR(100) NOT NULL,
        role ENUM('admin', 'manager', 'user') DEFAULT 'user',
        status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_login TIMESTAMP NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建基金公司表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS companies (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        code VARCHAR(20) UNIQUE NOT NULL,
        establishment_date DATE,
        total_assets DECIMAL(20,2) DEFAULT 0,
        fund_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建基金经理表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS managers (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        company_name VARCHAR(100),
        experience_years INT DEFAULT 0,
        fund_count INT DEFAULT 0,
        avg_return DECIMAL(5,2) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建基金表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS funds (
        id INT PRIMARY KEY AUTO_INCREMENT,
        code VARCHAR(20) UNIQUE NOT NULL,
        name VARCHAR(200) NOT NULL,
        company_id INT,
        manager_id INT,
        company_name VARCHAR(100),
        manager_name VARCHAR(100),
        fund_type VARCHAR(50),
        category VARCHAR(50),
        risk_level ENUM('低风险', '中低风险', '中风险', '中高风险', '高风险'),
        tags TEXT,
        nav DECIMAL(10,4),
        nav_date DATE,
        total_assets DECIMAL(20,2),
        establishment_date DATE,
        status ENUM('active', 'inactive') DEFAULT 'active',
        daily_return DECIMAL(5,2),
        weekly_return DECIMAL(5,2),
        monthly_return DECIMAL(5,2),
        yearly_return DECIMAL(5,2),
        max_drawdown DECIMAL(5,2),
        sharpe_ratio DECIMAL(5,2),
        benchmark VARCHAR(100),
        management_fee DECIMAL(5,2),
        custodian_fee DECIMAL(5,2),
        subscription_fee DECIMAL(5,2),
        redemption_fee DECIMAL(5,2),
        min_investment DECIMAL(10,2),
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (company_id) REFERENCES companies(id),
        FOREIGN KEY (manager_id) REFERENCES managers(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建基金净值表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_nav (
        id INT PRIMARY KEY AUTO_INCREMENT,
        fund_id INT NOT NULL,
        nav_date DATE NOT NULL,
        nav DECIMAL(10,4) NOT NULL,
        cumulative_nav DECIMAL(10,4),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (fund_id) REFERENCES funds(id),
        UNIQUE KEY unique_fund_date (fund_id, nav_date)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建基金持仓表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_holdings (
        id INT PRIMARY KEY AUTO_INCREMENT,
        fund_id INT NOT NULL,
        stock_code VARCHAR(20),
        stock_name VARCHAR(100),
        weight DECIMAL(5,2),
        shares BIGINT,
        market_value DECIMAL(20,2),
        report_date DATE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (fund_id) REFERENCES funds(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建基金标签表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS fund_tags (
        id INT PRIMARY KEY AUTO_INCREMENT,
        fund_id INT NOT NULL,
        tag VARCHAR(50) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (fund_id) REFERENCES funds(id),
        UNIQUE KEY unique_fund_tag (fund_id, tag)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建因子表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS factors (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        formula TEXT,
        category VARCHAR(50),
        type ENUM('basic', 'derived') DEFAULT 'basic',
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (created_by) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建因子关系表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS factor_relations (
        id INT PRIMARY KEY AUTO_INCREMENT,
        derived_factor_id INT NOT NULL,
        base_factor_id INT NOT NULL,
        weight DECIMAL(5,2) DEFAULT 1.0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (derived_factor_id) REFERENCES factors(id),
        FOREIGN KEY (base_factor_id) REFERENCES factors(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建策略表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS strategies (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        type VARCHAR(50),
        config JSON,
        status ENUM('active', 'inactive', 'testing') DEFAULT 'active',
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (created_by) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建策略基金配置表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS strategy_funds (
        id INT PRIMARY KEY AUTO_INCREMENT,
        strategy_id INT NOT NULL,
        fund_id INT NOT NULL,
        weight DECIMAL(5,2) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (strategy_id) REFERENCES strategies(id),
        FOREIGN KEY (fund_id) REFERENCES funds(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建策略回测表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS strategy_backtest (
        id INT PRIMARY KEY AUTO_INCREMENT,
        strategy_id INT NOT NULL,
        start_date DATE,
        end_date DATE,
        initial_capital DECIMAL(20,2),
        final_capital DECIMAL(20,2),
        total_return DECIMAL(10,4),
        annual_return DECIMAL(10,4),
        max_drawdown DECIMAL(10,4),
        sharpe_ratio DECIMAL(10,4),
        test_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (strategy_id) REFERENCES strategies(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建组合表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS portfolios (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        risk_level ENUM('低', '中', '高'),
        strategy_id INT,
        status ENUM('pending', 'approved', 'rejected', 'active') DEFAULT 'pending',
        created_by INT,
        audit_by INT,
        audit_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (strategy_id) REFERENCES strategies(id),
        FOREIGN KEY (created_by) REFERENCES users(id),
        FOREIGN KEY (audit_by) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建组合基金配置表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS portfolio_funds (
        id INT PRIMARY KEY AUTO_INCREMENT,
        portfolio_id INT NOT NULL,
        fund_id INT NOT NULL,
        weight DECIMAL(5,2) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (portfolio_id) REFERENCES portfolios(id),
        FOREIGN KEY (fund_id) REFERENCES funds(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建组合收益表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS portfolio_performance (
        id INT PRIMARY KEY AUTO_INCREMENT,
        portfolio_id INT NOT NULL,
        date DATE NOT NULL,
        nav DECIMAL(10,4),
        cumulative_return DECIMAL(10,4),
        daily_return DECIMAL(10,4),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (portfolio_id) REFERENCES portfolios(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建审核日志表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS audit_logs (
        id INT PRIMARY KEY AUTO_INCREMENT,
        portfolio_id INT NOT NULL,
        action VARCHAR(50) NOT NULL,
        comment TEXT,
        user_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (portfolio_id) REFERENCES portfolios(id),
        FOREIGN KEY (user_id) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建交易单表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS trade_orders (
        id INT PRIMARY KEY AUTO_INCREMENT,
        portfolio_id INT NOT NULL,
        type ENUM('rebalance', 'error_handling') NOT NULL,
        status ENUM('pending', 'executed', 'rejected') DEFAULT 'pending',
        reason TEXT,
        created_by INT NOT NULL,
        executed_by INT,
        rejected_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        executed_at TIMESTAMP NULL,
        rejected_at TIMESTAMP NULL,
        reject_reason TEXT,
        FOREIGN KEY (portfolio_id) REFERENCES portfolios(id),
        FOREIGN KEY (created_by) REFERENCES users(id),
        FOREIGN KEY (executed_by) REFERENCES users(id),
        FOREIGN KEY (rejected_by) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建交易单明细表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS trade_order_details (
        id INT PRIMARY KEY AUTO_INCREMENT,
        order_id INT NOT NULL,
        fund_id INT NOT NULL,
        action ENUM('buy', 'sell') NOT NULL,
        quantity INT NOT NULL,
        price DECIMAL(10,4),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (order_id) REFERENCES trade_orders(id),
        FOREIGN KEY (fund_id) REFERENCES funds(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建交割单表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS settlements (
        id INT PRIMARY KEY AUTO_INCREMENT,
        order_id INT NOT NULL,
        fund_id INT NOT NULL,
        action ENUM('buy', 'sell') NOT NULL,
        quantity INT NOT NULL,
        price DECIMAL(10,4),
        status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
        new_fund_id INT,
        handled_by INT,
        handled_at TIMESTAMP NULL,
        handle_reason TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (order_id) REFERENCES trade_orders(id),
        FOREIGN KEY (fund_id) REFERENCES funds(id),
        FOREIGN KEY (new_fund_id) REFERENCES funds(id),
        FOREIGN KEY (handled_by) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建用户组合表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS user_portfolios (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        tags JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    console.log('✅ 所有数据表创建成功');

    // 插入初始数据
    await insertInitialData(connection);
    console.log('✅ 初始数据插入成功');

    console.log('🎉 数据库初始化完成！');

  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

async function insertInitialData(connection) {
  // 插入管理员用户
  const bcrypt = require('bcryptjs');
  const hashedPassword = await bcrypt.hash('admin', 12);
  
  await connection.execute(`
    INSERT IGNORE INTO users (username, password, email, name, role, status) 
    VALUES ('admin', ?, 'admin@example.com', '系统管理员', 'admin', 'active')
  `, [hashedPassword]);

  // 插入测试用户
  const testPassword = await bcrypt.hash('123456', 12);
  await connection.execute(`
    INSERT IGNORE INTO users (username, password, email, name, role, status) 
    VALUES ('test', ?, 'test@example.com', '测试用户', 'user', 'active')
  `, [testPassword]);

  // 插入基金公司示例数据
  await connection.execute(`
    INSERT IGNORE INTO companies (name, code, establishment_date, total_assets, fund_count) VALUES
    ('华夏基金管理有限公司', 'HX', '1998-04-09', 1000000000000.00, 150),
    ('易方达基金管理有限公司', 'YFD', '2001-04-17', 800000000000.00, 120),
    ('嘉实基金管理有限公司', 'JS', '1999-03-25', 600000000000.00, 100),
    ('招商基金管理有限公司', 'ZS', '2002-12-27', 400000000000.00, 80),
    ('中欧基金管理有限公司', 'ZO', '2006-07-19', 300000000000.00, 60),
    ('汇添富基金管理有限公司', 'HTF', '2005-02-03', 500000000000.00, 90)
  `);

  // 插入基金经理示例数据
  await connection.execute(`
    INSERT IGNORE INTO managers (name, company_name, experience_years, fund_count, avg_return) VALUES
    ('张坤', '易方达基金', 15, 8, 18.5),
    ('萧楠', '易方达基金', 12, 6, 16.8),
    ('刘彦春', '嘉实基金', 18, 10, 20.2),
    ('侯昊', '招商基金', 10, 4, 12.5),
    ('葛兰', '中欧基金', 8, 5, 25.6),
    ('刘江', '汇添富基金', 11, 7, 19.3),
    ('蔡向阳', '华夏基金', 16, 9, 14.7)
  `);

  // 插入基金示例数据（匹配前端mock数据）
  await connection.execute(`
    INSERT IGNORE INTO funds (
      code, name, company_name, manager_name, fund_type, category, risk_level, 
      tags, nav, nav_date, total_assets, establishment_date, status,
      daily_return, weekly_return, monthly_return, yearly_return,
      max_drawdown, sharpe_ratio, benchmark,
      management_fee, custodian_fee, subscription_fee, redemption_fee,
      min_investment, description
    ) VALUES
    ('000001', '华夏成长混合', '华夏基金', '张坤', '混合型', '成长型', '中高风险',
     '["成长型", "混合型", "消费行业", "价值投资"]', 2.856, CURDATE(), 125.6, '2001-12-18', 'active',
     1.25, 3.45, 8.92, 15.67, -12.34, 1.23, '沪深300指数',
     1.5, 0.25, 1.2, 0.5, 1000, '专注于成长型股票投资，追求长期资本增值'),
    
    ('110022', '易方达消费行业股票', '易方达基金', '萧楠', '股票型', '行业主题', '高风险',
     '["消费行业", "股票型", "主题投资", "白酒"]', 3.245, CURDATE(), 98.3, '2010-08-20', 'active',
     -0.85, 2.18, 6.34, 18.92, -15.67, 1.45, '中证消费指数',
     1.5, 0.25, 1.5, 0.5, 1000, '专注于消费行业投资，重点配置白酒、家电等消费龙头'),
    
    ('000002', '嘉实增长混合', '嘉实基金', '刘彦春', '混合型', '增长型', '中高风险',
     '["增长型", "混合型", "科技股", "医药"]', 4.123, CURDATE(), 156.8, '2003-07-09', 'active',
     0.92, 4.67, 12.45, 22.34, -18.92, 1.67, '中证800指数',
     1.5, 0.25, 1.2, 0.5, 1000, '追求长期增长，重点投资成长性良好的优质企业'),
    
    ('161725', '招商中证白酒指数', '招商基金', '侯昊', '指数型', '行业指数', '高风险',
     '["白酒指数", "指数型", "行业主题", "被动投资"]', 1.234, CURDATE(), 89.2, '2015-05-27', 'active',
     -1.23, -2.45, -5.67, -8.92, -25.34, 0.89, '中证白酒指数',
     0.6, 0.1, 0.8, 0.5, 1000, '跟踪中证白酒指数，投资白酒行业龙头企业'),
    
    ('000858', '五粮液', '易方达基金', '张坤', '股票型', '个股', '高风险',
     '["个股", "股票型", "白酒", "价值投资"]', 156.78, CURDATE(), 45.6, '2001-04-20', 'active',
     2.34, 8.92, 15.67, 28.45, -22.34, 1.89, '沪深300指数',
     1.5, 0.25, 1.5, 0.5, 1000, '专注于五粮液股票投资，分享白酒行业成长红利'),
    
    ('000963', '华夏医疗健康混合', '中欧基金', '葛兰', '混合型', '行业主题', '中高风险',
     '["医疗健康", "混合型", "医药", "创新药"]', 2.456, CURDATE(), 67.8, '2016-09-29', 'active',
     1.67, 5.23, 14.56, 32.78, -16.78, 1.78, '中证医药指数',
     1.5, 0.25, 1.2, 0.5, 1000, '专注于医疗健康行业投资，重点配置创新药、医疗器械等'),
    
    ('001766', '汇添富创新医药混合', '汇添富基金', '刘江', '混合型', '行业主题', '中高风险',
     '["创新医药", "混合型", "医药", "生物科技"]', 1.987, CURDATE(), 34.5, '2016-09-29', 'active',
     0.89, 3.45, 9.23, 25.67, -14.56, 1.56, '中证医药指数',
     1.5, 0.25, 1.2, 0.5, 1000, '投资创新医药企业，分享医药行业创新发展红利'),
    
    ('002001', '华夏回报混合', '华夏基金', '蔡向阳', '混合型', '平衡型', '中风险',
     '["平衡型", "混合型", "稳健", "分红"]', 3.456, CURDATE(), 78.9, '2003-09-05', 'active',
     0.45, 2.34, 6.78, 12.45, -8.92, 1.34, '沪深300指数',
     1.5, 0.25, 1.2, 0.5, 1000, '追求稳健收益，注重风险控制，适合稳健型投资者')
  `);

  // 插入因子示例数据
  await connection.execute(`
    INSERT IGNORE INTO factors (name, description, category, type) VALUES
    ('动量因子', '衡量基金价格动量表现的因子', '技术因子', 'basic'),
    ('价值因子', '衡量基金价值投资特征的因子', '基本面因子', 'basic'),
    ('规模因子', '衡量基金规模效应的因子', '风险因子', 'basic'),
    ('质量因子', '衡量基金投资质量的因子', '基本面因子', 'basic')
  `);

  console.log('✅ 示例数据插入完成');
}

// 运行初始化
initDatabase(); 